* I went through this code with Jaffe on 3.30.21, and added some comments;
*  In sas, comments start with * and end in ;  /* Or, for multi-line comments
/* they start with the backslash asterisk and end with */
/* Remember sas is database -organized.  There are 2 types of steps: Data steps: data creation and manipulation 
/*                                          and proc steps -- analysis.    */
* ~/CRSP/sas/mymerged.sas     3.30.21;
*  copied from ~/MyWRDS/progs/mymerged.sas  2/8/19    ;
* I ran this on 2.18.22.  I had to remove all comments inside a SQL statement.;
* Ouptput is 4418054 lines.;

libname lib '/scratch/arizona/lam/';
libname mine '/home/arizona/lamfin/';

proc import datafile="/home/arizona/lamfin/BSVMasterDate.txt" out=masdates replace;
     getnames=yes;
run;
    
data mdates;
set masdates;
date = input(put(mdate, 8.), yymmdd8.);          * mdate is a column header -- therefore implicitly defined when we created that data set.;
format date date9.;
monthnum = intck("month", "01JAN1955"d, date);   * intck converts ;
* drop mdate;
keep mdate monthnum;
run;

data rdlist;
    set crsp.msedelist;                          * CRSP delisting table;
*    if dlret < -1.0 and hexcd=3 then dlret = -0.5;
*    if dlret < -1.0 then dlret= -0.3;
    monthnum = intck("month", "01JAN1955"d, dlstdt);
    keep permno dlstdt dlstcd nwperm  dlret hexcd monthnum;
run;

data dists;                                      * CRSP distribution information.;
    set crsp.msedist;
    monthnum = intck("month", "01JAN1955"d, exdt);    * Note the usage of the function intck in a data step;
    keep permno distcd divamt exdt monthnum;
run;

proc sql;                                        *   dlist is the name of the new table  AS here tells how to structure the table.;
    CREATE TABLE dlist AS                        
        SELECT a.mdate, a.monthnum,
        b.monthnum, b.permno, b.dlstcd, b.dlret, b.hexcd
        FROM mdates AS a LEFT JOIN rdlist AS b
        ON a.monthnum = b.monthnum
        WHERE b.permno IS NOT MISSING AND b.dlstcd IS NOT MISSING AND
        b.dlret IS NOT MISSING AND year(b.dlstdt) >= 1960
        order by b.permno, a.mdate;
quit;

proc sql;
    CREATE TABLE crspraw AS
        SELECT a.permno, a.permco, a.date, a.ret, a.prc, a.shrout,
        b.shrcd, b.siccd, intck("month", "01JAN1955"d, a.date) as monthnum        
        FROM crsp.msf AS a LEFT JOIN crsp.msenames AS b                          
        ON a.permno = b.permno AND b.namedt <= a.date <= b.nameendt
        WHERE a.permno IS NOT MISSING AND a.date IS NOT MISSING AND
        a.ret IS NOT MISSING AND year(a.date) >= 1955
        order by a.permno, a.date;
quit;

data crone;
    set crspraw dlist;                                                            * Stacks the 2 files (vertically appending)   ;
run;

proc sql;
    CREATE TABLE mycrsp AS
      SELECT *
      FROM crone as a left join dists as b
     ON a.permno = b.permno AND a.monthnum = b.monthnum
     ORDER BY a.permno, a.date;

proc sort data=crsp.ccmxpf_lnkhist out=lnk;                                       * Link table for CRSP / compustat merging.  lnk is a table: GVKEY, LINKPRIM,LIID,LINKTYPE,LPERMNO,LPERMCO,LINKDT,LINKENDDT ;
    where LINKTYPE in ("LU", "LC");                                               * Keep only those that are coded LU and LC   All the fields exist in this data set lnk ;
    by GVKEY LINKDT;                                                              *  Sort on GVKEY and within that by LINKDT ;
run;

proc sql; 
    CREATE TABLE mydata AS
        SELECT a.gvkey, a.datadate, a.fyr, a.cusip, a.at, a.lt, a.pstkl, a.pstkrv,      
        a.upstk, a.txditc, a.csho, b.lpermno,
        intck("month", "01JAN1955"d, datadate) as monthnum
        FROM comp.funda as a inner join lnk as b
        ON a.gvkey = b.gvkey AND (b.linkdt <= a.datadate or b.linkdt = .B) AND
        (a.datadate <= b.linkenddt or b.linkenddt = .E)
                                                                                       
        WHERE indfmt = 'INDL' AND datafmt = 'STD' AND popsrc = 'D' AND consol = 'C'   
                                                                                     
        ORDER BY b.lpermno, a.datadate;

    CREATE TABLE lib.merged AS
        SELECT *
        FROM mycrsp as a left join mydata as b
        on a.permno = b.lpermno
/*         AND b.monthnum + 6 <= a.monthnum < b.monthnum + 18 */                       
        AND b.monthnum = a.monthnum
        ORDER by a.permno, a.monthnum;
quit;

proc export data=lib.merged
    outfile='/scratch/arizona/lam/mymerged_2_18_21.csv'
    DBMS=csv
    REPLACE;
quit;
